id_array = []
finput = open("input.txt", "r")
while !finput.eof do
  id_array << finput.readline.chomp
  #puts finput.readline
end
finput.close

ids = id_array.join(", ")

finput = open("channel.txt", "r")
sql_temp = " (SELECT MerchantID, COUNT(*) AS c%sTotal FROM C%sMerchantBidProduct c%s GROUP BY MerchantID) m%s ON m.MerchantID=m%s.MerchantID "
chs = []
sqls = []
while !finput.eof do
  ch = finput.readline.chomp
  chs << "m%s.c%sTotal" % [ch, ch]
  sqls << sql_temp % [ch, ch, ch, ch, ch]
end
finput.close

sql = "SELECT m.MerchantID, %s FROM Merchant m \nLEFT JOIN " % chs.join(", ");
sql += sqls.join("\nLEFT JOIN")
sql += "\nWHERE m.MerchantID IN (%s)" % ids

open("merchant_report.sql", "w") { |f| f.write(sql) }